트랜잭트 SQL
"오늘의AI위키"의 AI를 통해 더욱 풍부하고 폭넓은 지식 경험을 누리세요.
1. 개요
트랜잭트 SQL은 지역 변수 선언, 흐름 제어, 데이터 조작 및 대량 데이터 로딩을 위한 기능을 제공하는 SQL의 확장된 버전이다. 지역 변수와 전역 변수를 사용하며, `DECLARE`, `SET`, `SELECT` 문을 통해 변수를 선언하고 값을 할당한다. 흐름 제어 키워드로는 `BEGIN`/`END`, `IF`/`ELSE`, `WHILE`, `BREAK`, `CONTINUE`, `GOTO`, `RETURN`, `WAITFOR` 등이 있다. 또한 `DELETE` 및 `UPDATE` 문에 `FROM` 절을 추가하여 조인을 통한 데이터 조작을 지원하며, `BULK INSERT`를 통한 대량 데이터 로딩, `TRY...CATCH`를 이용한 예외 처리, `CURSOR`를 이용한 행 단위 데이터 처리 기능을 제공한다. 하지만 SQL 표준을 벗어나는 확장 기능과 절차적 기능 확장이 SQL의 선언적 특성을 저해한다는 비판도 존재한다.
더 읽어볼만한 페이지
- 데이터 중심 프로그래밍 언어 - 마이크로소프트 액세스
마이크로소프트 액세스는 1992년 출시된 데이터베이스 관리 시스템으로, 테이블, 쿼리, 폼 등을 생성하고 VBA를 통해 솔루션을 개발하며, 윈도우에서 사용 가능하고 다양한 데이터 형식과 통합된다. - 데이터 중심 프로그래밍 언어 - SQL
SQL은 관계형 데이터베이스 관리 시스템에서 데이터를 관리하고 조작하기 위해 설계된 표준 프로그래밍 언어로서, 데이터 정의어, 데이터 조작어, 데이터 제어어를 포함하는 다양한 명령어 문법을 제공하며 ANSI와 ISO에 의해 표준으로 채택되었다. - SQL - 사용자 정의 함수
사용자 정의 함수는 프로그래밍 언어와 데이터베이스 시스템에서 사용자가 직접 정의하여 재사용할 수 있는 코드 블록이다. - SQL - 임베디드 SQL
임베디드 SQL은 호스트 프로그래밍 언어 내에 SQL 문을 삽입하여 데이터베이스와 상호 작용하는 기술로, 데이터베이스 액세스를 표준화하지만 보안 취약점과 이식성 저하의 단점도 가진다.
| 트랜잭트 SQL | |
|---|---|
| 일반 정보 | |
| 종류 | 데이터베이스 관리 시스템 (DBMS) |
| 개발사 | 마이크로소프트 (SQL Server) 사이베이스 (Sybase ASE) |
| 첫 출시 | 1989년 |
| 최신 버전 | SQL Server: SQL Server 2022 (2022년 11월 16일) Sybase ASE: 16.0 SP04 PL06 (2023년 10월 31일) |
| 운영 체제 | 윈도우 리눅스 |
| 프로그래밍 언어 | C++ |
| 데이터베이스 언어 | T-SQL |
| 라이선스 | 상업 라이선스 |
| 특징 | |
| 특징 | SQL 표준의 확장 저장 프로시저 지원 트랜잭션 지원 트리거 지원 커서 지원 |
| 호환성 | |
| 호환성 | ANSI SQL ISO SQL |
| 관련 표준 | |
| 관련 표준 | SQL |
2. 변수
Transact-SQL(T-SQL)에서는 스크립트 내에서 값을 임시로 저장하고 조작하기 위해 변수를 사용한다. 변수는 사용 범위와 정의 방식에 따라 지역 변수와 전역 변수로 구분된다.
지역 변수는 사용자가 `DECLARE` 문을 이용해 직접 선언하며, 변수 이름 앞에 `@` 기호를 붙인다. `SET` 또는 `SELECT` 문으로 값을 할당하고, 선언된 스크립트나 프로시저 등의 범위 내에서만 유효하다.
전역 변수는 SQL Server 시스템이 미리 정의한 변수로, 주로 `@@` 기호로 시작하며 서버의 상태나 마지막 실행 결과(`@@ERROR`, `@@ROWCOUNT` 등) 같은 정보를 제공한다. 사용자가 직접 전역 변수를 정의할 수는 없다.
2. 1. 지역 변수
트랜잭트 SQL에서는 지역 변수를 선언하고 사용하기 위해 `DECLARE`, `SET`, `SELECT` 문을 제공한다.변수를 선언할 때는 `DECLARE` 문을 사용하여 변수 이름과 데이터 형식을 지정한다. 변수 이름 앞에는 `@` 기호를 붙인다.
DECLARE @var1 NVARCHAR(30);
선언된 변수에 값을 할당하는 방법은 두 가지가 있다.
1. `SET` 문을 사용하여 직접 값을 할당한다.
SET @var1 = 'Some Name';
2. `SELECT` 문을 사용하여 쿼리의 결과를 변수에 할당할 수도 있다.
SELECT @var1 = Name
FROM Sales.Store
WHERE CustomerID = 100;
값을 할당한 후에는 스크립트 내에서 변수 이름을 사용하여 해당 값을 참조할 수 있다.
다음은 정수형 변수를 선언하고 초기화한 뒤, WHILE 루프에서 사용하는 예시이다.
DECLARE @Counter INT
SET @Counter = 10
WHILE @Counter > 0
BEGIN
PRINT 'The count is ' + CONVERT(VARCHAR(10), @Counter)
SET @Counter = @Counter - 1
END
이 코드는 `@Counter` 변수 값을 10으로 초기화하고, 루프를 돌면서 변수 값을 출력하고 1씩 감소시킨다. `@Counter`가 0보다 클 동안 루프가 실행된다.
`SELECT` 문을 사용하여 변수를 초기화하는 다른 예시는 다음과 같다.
DECLARE @ArticleCount INT
SELECT @ArticleCount = COUNT(*) FROM Articles
INSERT INTO SizeLog (SampleTime, ArticleCount) VALUES (GETDATE(), @ArticleCount)
이 코드는 `Articles` 테이블의 전체 행 수를 `@ArticleCount` 변수에 저장한 다음, 현재 시간과 함께 `SizeLog` 테이블에 새로운 행으로 삽입한다.
지역 변수는 선언된 스크립트나 프로시저, 함수 등의 실행 범위 내에서만 유효하다. 트랜잭트 SQL은 사용자가 직접 정의하는 전역 변수를 지원하지 않는다.
2. 2. 전역 변수
전역 변수는 실행 중인 스크립트 내에서 다양한 상태를 감시하고 정보를 얻는 데 사용된다. Transact-SQL에서 전역 변수는 일반적으로 `@@` 기호로 시작한다.자주 사용되는 전역 변수는 다음과 같다.
| 변수명 | 설명 |
|---|---|
| `@@ERROR` | 바로 직전에 실행된 쿼리의 오류 상태를 저장한다. 값이 0이면 오류가 없음을 의미한다. |
| `@@ROWCOUNT` | 바로 직전에 실행된 쿼리에 의해 영향을 받은 행(레코드)의 수를 저장한다. |
| `@@FETCH_STATUS` | 현재 실행 중인 커서의 FETCH 작업 상태를 저장한다. 값이 0이면 FETCH 작업이 성공적으로 완료되었음을 나타낸다. |
다음은 전역 변수를 활용한 오류 처리 예시이다.
- '''쿼리 실행 시 오류 처리'''
DECLARE @ERROR_STATUS INT
SET @ERROR_STATUS = 0
- - 예시 쿼리 실행
SELECT DATE
FROM CALENDAR WITH (NOLOCK)
WHERE YEAR = '2007' AND MONTH = '01'
- - @@ERROR 전역 변수를 사용하여 직전 쿼리의 오류 상태 확인
- - 0이 아니면 오류 발생
SET @ERROR_STATUS = @@ERROR
IF @ERROR_STATUS <> 0
BEGIN
PRINT '오류가 발생했습니다.'
RETURN -- 오류 발생 시 스크립트 중단
END
- '''UPDATE 실행 시 해당되는 데이터가 없는 경우 처리'''
DECLARE @ROW_COUNT INT
SET @ROW_COUNT = 0
- - 예시 UPDATE 실행
UPDATE CALENDAR
SET DATE = GETDATE()
WHERE YEAR = '2007' AND MONTH = '01'
- - @@ROWCOUNT 전역 변수를 사용하여 직전 UPDATE 쿼리로 변경된 행 수 확인
SET @ROW_COUNT = @@ROWCOUNT
IF @ROW_COUNT = 0
BEGIN
PRINT '업데이트된 레코드가 없습니다.'
RETURN -- 변경된 레코드가 없을 경우 스크립트 중단 또는 다른 처리
END
3. 흐름 제어
T-SQL은 코드의 실행 흐름을 제어하기 위한 여러 키워드를 제공한다. 이를 통해 조건에 따라 다른 코드를 실행하거나 특정 코드 블록을 반복하는 등의 복잡한 논리를 구현할 수 있다.
주요 흐름 제어 키워드는 다음과 같다.
- `IF...ELSE`: 조건에 따라 실행할 코드 블록을 결정한다.
- `WHILE`: 특정 조건이 참인 동안 코드 블록을 반복 실행한다.
- `BEGIN...END`: 여러 개의 T-SQL 문을 하나의 논리적인 문 블록으로 묶는다. `IF`나 `WHILE` 등 다른 제어문과 함께 사용되어 여러 문장을 하나의 단위로 처리할 수 있게 한다.
- `BREAK와 CONTINUE`: `WHILE` 루프 내에서 사용된다. `BREAK`는 루프를 즉시 종료하고, `CONTINUE`는 현재 반복을 중단하고 다음 반복으로 넘어간다.
- `GOTO`: 코드 내의 특정 레이블(Label)로 실행 흐름을 이동시킨다.
- `RETURN`: 저장 프로시저나 함수 내에서 실행을 즉시 중단하고 호출한 곳으로 돌아간다. 선택적으로 정수 값을 반환할 수 있다.
- `WAITFOR`: 지정된 시간 동안 실행을 지연시키거나 특정 시각까지 실행을 대기시킨다.
3. 1. IF...ELSE
`IF`와 `ELSE`는 트랜잭트 SQL의 흐름 제어 키워드 중 하나로, 조건에 따라 다른 코드 블록을 실행할 수 있게 한다.다음 예시는 현재 날짜의 요일을 확인하여 주말(토요일 또는 일요일)인지 평일인지에 따라 다른 메시지를 출력한다. 이 코드는 시스템 설정 `@@DATEFIRST`에서 일요일(값 1)을 주의 시작일로 간주한다고 가정한다.
IF DATEPART(dw, GETDATE()) = 7 OR DATEPART(dw, GETDATE()) = 1 -- DATEPART(dw, ...)는 요일을 숫자로 반환 (일요일=1, 토요일=7로 가정)
PRINT 'It is the weekend.' -- 조건이 참(주말)일 경우 이 메시지를 출력한다.
ELSE
PRINT 'It is a weekday.' -- 조건이 거짓(평일)일 경우 이 메시지를 출력한다.
조건에 따라 여러 개의 문장을 실행해야 할 경우, `BEGIN`과 `END` 키워드를 사용하여 문 블록을 정의할 수 있다.
IF DATEPART(dw, GETDATE()) = 7 OR DATEPART(dw, GETDATE()) = 1
BEGIN -- 조건이 참일 때 실행될 문장 블록 시작
PRINT 'It is the weekend.';
PRINT 'Get some rest!'; -- 주말 관련 추가 메시지
END -- 조건이 참일 때 실행될 문장 블록 끝
ELSE
BEGIN -- 조건이 거짓일 때 실행될 문장 블록 시작
PRINT 'It is a weekday.';
PRINT 'Get to work!'; -- 평일 관련 추가 메시지
END -- 조건이 거짓일 때 실행될 문장 블록 끝
3. 2. WHILE
`WHILE` 문은 주어진 조건이 참(불리언 값 `TRUE`)인 동안 특정 코드 블록을 반복적으로 실행하는 데 사용된다.기본적인 구문은 다음과 같다.
WHILE Boolean_expression
BEGIN
- - SQL 문 또는 문 블록
- - BREAK 또는 CONTINUE 문 사용 가능
END
`BEGIN`과 `END` 키워드는 반복될 코드 블록의 시작과 끝을 나타낸다. 만약 반복될 코드가 단일 문장이라면 `BEGIN`과 `END`는 생략할 수 있다.
다음은 `WHILE` 문을 사용하여 지역 변수 `@i`가 5보다 작을 때까지 "Hello world."를 출력하는 예제이다. 지역 변수 `@i`는 `DECLARE` 문으로 선언되고 `SET` 문으로 초기화 및 변경된다.
DECLARE @i INT;
SET @i = 0;
WHILE @i < 5
BEGIN
PRINT 'Hello world.';
SET @i = @i + 1;
END;
또 다른 예로, 변수 `@Counter`를 10에서 1까지 감소시키면서 각 단계의 값을 출력하는 코드는 다음과 같다.
DECLARE @Counter INT
SET @Counter = 10
WHILE @Counter > 0
BEGIN
PRINT 'The count is ' + CONVERT(VARCHAR(10), @Counter)
SET @Counter = @Counter - 1
END
`WHILE` 루프 내에서 흐름을 제어하기 위해 `BREAK`와 `CONTINUE` 문을 사용할 수 있다.
- `BREAK`: 현재 실행 중인 `WHILE` 루프를 즉시 종료시킨다. `END` 키워드 다음의 문장부터 실행을 계속한다.
- `CONTINUE`: `WHILE` 루프의 현재 반복을 중단하고, 루프의 다음 반복으로 건너뛴다. 즉, `WHILE` 키워드 다음의 조건 검사부터 다시 시작한다.
3. 3. BEGIN...END
`BEGIN`과 `END`는 여러 T-SQL 문을 하나의 문 블록으로 묶는 데 사용된다. 이것은 특정 조건이나 반복문 내에서 여러 명령을 하나의 단위로 처리할 수 있게 한다.예를 들어, `IF` 문과 함께 사용하여 조건에 따라 여러 문장을 실행해야 할 경우, 다음과 같이 `BEGIN`과 `END`로 해당 문장들을 묶어준다.
IF DATEPART(dw, GETDATE()) = 7 OR DATEPART(dw, GETDATE()) = 1 -- 날짜가 주말(토요일=7 또는 일요일=1)인지 확인
BEGIN
PRINT 'It is the weekend.'; -- 주말 메시지 출력
PRINT 'Get some rest on the weekend!'; -- 추가 주말 메시지 출력
END
ELSE -- 주말이 아닐 경우 (평일)
BEGIN
PRINT 'It is a weekday.'; -- 평일 메시지 출력
PRINT 'Get to work on a weekday!'; -- 추가 평일 메시지 출력
END;
위 코드에서 `BEGIN`과 `END` 블록이 없다면, `IF` 또는 `ELSE` 다음의 첫 번째 `PRINT` 문만 조건에 따라 실행되고, 두 번째 `PRINT` 문은 조건과 상관없이 항상 실행될 것이다. `BEGIN...END`는 이처럼 여러 문장을 논리적으로 그룹화하여 코드의 흐름을 제어하는 데 필수적이다.
3. 4. BREAK와 CONTINUE
'BREAK'는 현재 실행 중인 'WHILE' 루프를 즉시 종료시키는 명령어이다.[1] 반면, 'CONTINUE'는 현재 반복을 중단하고 'WHILE' 루프의 다음 반복으로 건너뛰게 하는 명령어이다.[1]3. 5. RETURN
RETURN은 저장 프로시저나 함수에서 즉시 실행을 종료하고 반환할 때 사용한다.3. 6. WAITFOR
WAITFOR는 지정된 시간 동안 대기하거나 특정 시간이 될 때까지 실행을 멈추게 하는 데 사용된다. 이를 통해 실행을 지연시키거나, 정해진 시간까지 실행을 차단할 수 있다.4. DELETE, UPDATE 문 확장
트랜잭트 SQL에서는 DELETE 문과 UPDATE 문이 확장되어, 하위 쿼리를 사용하지 않고도 다른 테이블의 데이터를 조작하는 데 사용할 수 있다. 이 확장 기능의 핵심은 `DELETE` 및 `UPDATE` 문에 `FROM` 절을 추가할 수 있다는 점이다.
- '''DELETE''' 문: SELECT 문과 유사하게 `FROM` 절에서 조인된 테이블을 사용할 수 있다. 이때 삭제할 테이블의 이름이나 별칭은 `DELETE` 키워드와 `FROM` 절 사이에 명시한다.
- '''UPDATE''' 문: `FROM` 절을 추가할 수 있다. 업데이트할 테이블은 `FROM` 절에서 다른 테이블과 조인하고 별칭으로 참조하거나, 표준 SQL처럼 문장 시작 부분에서 참조할 수 있다.
다음은 `users` 테이블에서 `user_flags` 테이블에 'idle' 플래그가 지정된 모든 사용자를 삭제하는 예시이다.
DELETE u
FROM users AS u
INNER JOIN user_flags AS f ON u.id = f.id
WHERE f.name = 'idle';
5. BULK INSERT
BULK INSERT는 대량의 데이터 적재 처리, 여러 행을 테이블로 삽입, 외부 순차 파일로부터 데이터 읽기를 구현하는 트랜잭트 SQL 문이다. BULK INSERT를 사용하면 추가할 각 행에 대해 개별 INSERT 문을 실행하는 프로세스보다 성능이 향상된다. 자세한 내용은 [http://msdn2.microsoft.com/en-us/library/ms188365.aspx MSDN]에서 확인할 수 있다.
6. TRY...CATCH
SQL Server 2005부터[1], 마이크로소프트는 예외 처리 동작을 지원하기 위해 TRY...CATCH 로직을 도입했다. 이 기능을 통해 개발자는 각 SQL 실행문 이후에 @@ERROR 변수를 확인하는 번거로움 없이 코드를 더 단순하게 작성할 수 있다.TRY 블록 안에 있는 코드를 실행하다가 오류가 발생하면, 프로그램의 제어가 즉시 CATCH 블록으로 넘어가서 오류 처리 로직을 수행하게 된다. 예를 들어, 트랜잭션 중에 오류가 발생하면 CATCH 블록에서 해당 트랜잭션을 롤백(Rollback)하여 데이터 일관성을 유지할 수 있다.
아래는 TRY...CATCH 구문을 사용한 예시 코드이다.
BEGIN TRAN;
BEGIN TRY
INSERT INTO MYTABLE(NAME) VALUES ('ABC');
INSERT INTO MYTABLE(NAME) VALUES ('123');
COMMIT TRAN;
END TRY
BEGIN CATCH
ROLLBACK TRAN;
END CATCH;
7. 커서(CURSOR)
Transact-SQL에서는 테이블의 데이터를 한 행씩 순차적으로 처리해야 할 때 '''커서(CURSOR)'''를 사용할 수 있다. 커서는 특정 SQL 쿼리의 결과 집합을 가리키며, 이 집합 내의 행들을 하나씩 탐색하고 조작할 수 있게 해준다.
커서 사용은 일반적으로 다음과 같은 단계로 이루어진다.
# '''커서 선언 (DECLARE CURSOR)''': 사용할 커서의 이름과 커서가 참조할 SELECT 문을 정의한다.
# '''커서 열기 (OPEN)''': 선언된 커서를 열어 SELECT 문을 실행하고 결과 집합을 생성한다.
# '''데이터 가져오기 (FETCH)''': 커서가 가리키는 현재 행의 데이터를 변수로 읽어온다. `FETCH NEXT`는 다음 행으로 이동하며 데이터를 가져온다. 시스템 변수 `@@FETCH_STATUS`를 통해 데이터 가져오기의 성공 여부(0이면 성공)를 확인할 수 있다.
# '''데이터 처리''': `FETCH`로 가져온 데이터를 기반으로 필요한 로직을 수행한다. 보통 `WHILE @@FETCH_STATUS = 0` 루프를 사용하여 결과 집합의 끝에 도달할 때까지 반복 처리한다.
# '''커서 닫기 (CLOSE)''': 커서 사용이 끝나면 닫는다. 커서가 사용하던 일부 리소스가 해제되지만, 커서 정의는 메모리에 남아있다.
# '''커서 해제 (DEALLOCATE)''': 커서 정의를 메모리에서 완전히 제거하여 모든 관련 리소스를 해제한다.
아래는 `CALENDAR` 테이블에서 특정 조건(연도가 2006보다 큰 경우)을 만족하는 행의 `DATE` 열을 현재 날짜로 업데이트하는 커서 사용 예제이다.
DECLARE CUR_CALENDAR_UPDATE CURSOR FOR
SELECT YEAR, MONTH, DAY
FROM CALENDAR;
DECLARE @wk_year CHAR(4);
DECLARE @wk_month VARCHAR(2);
DECLARE @wk_day VARCHAR(2);
OPEN CUR_CALENDAR_UPDATE;
FETCH NEXT FROM CUR_CALENDAR_UPDATE INTO @wk_year, @wk_month, @wk_day;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @wk_year > '2006' -- CHAR 타입이므로 문자열 '2006'과 비교
BEGIN
UPDATE CALENDAR
SET DATE = GETDATE()
WHERE YEAR = @wk_year AND MONTH = @wk_month AND DAY = @wk_day;
END
FETCH NEXT FROM CUR_CALENDAR_UPDATE INTO @wk_year, @wk_month, @wk_day;
END
CLOSE CUR_CALENDAR_UPDATE;
DEALLOCATE CUR_CALENDAR_UPDATE;
8. 비판
PL/SQL과 마찬가지로, 트랜잭트 SQL은 기능을 추가하면서 SQL 표준과의 호환성을 해칠 뿐만 아니라, SQL이 본래 유지해야 할 모듈성을 파괴하고 있다는 비판을 받고 있다. 다시 말해, 트랜잭트 SQL의 추가 기능은 보통 프로그래밍 언어나 내장 SQL에 구현되어야 할 것들이다. 그렇기 때문에 제어 구조를 프로그래밍 언어와 SQL 양쪽에서 지정할 수 있게 되어 혼란을 야기한다.
본 사이트는 AI가 위키백과와 뉴스 기사,정부 간행물,학술 논문등을 바탕으로 정보를 가공하여 제공하는 백과사전형 서비스입니다.
모든 문서는 AI에 의해 자동 생성되며, CC BY-SA 4.0 라이선스에 따라 이용할 수 있습니다.
하지만, 위키백과나 뉴스 기사 자체에 오류, 부정확한 정보, 또는 가짜 뉴스가 포함될 수 있으며, AI는 이러한 내용을 완벽하게 걸러내지 못할 수 있습니다.
따라서 제공되는 정보에 일부 오류나 편향이 있을 수 있으므로, 중요한 정보는 반드시 다른 출처를 통해 교차 검증하시기 바랍니다.
문의하기 : help@durumis.com